package com.thinkingstar.iads.datacenter.entity.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;

import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.common.utils.SwapUtil;
import com.thinkingstar.iads.datacenter.entity.DcDesignContentMajor;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignContent;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignMajor;
import com.thinkingstar.iads.datacenter.entity.DcProjectMajorType;

/**
 * @ClassName: DaoDcProjectDesignContent 
 * @Description:数据中心_项目_项目设计内容维护
 * @author 1157372219@qq.com 
 * @date 2018年3月19日   
 */
@IocBean(args = {"refer:dao"})
public class DaoDcProjectDesignContent extends EntityRepositoryServiceImpl<DcProjectDesignContent>{

	public DaoDcProjectDesignContent(Dao dao) {
		super(dao);
	}
	
	
	/**
	 * 根据code查询项目设计内容
	 * @param code
	 * @return
	 */
	public DcProjectDesignContent getDesignContentByCode(String code){
		Criteria cri = Cnd.NEW();
		cri.where().and("project_design_content_code", "=", code);
		DcProjectDesignContent dcProjectDesignContent = dao().query(DcProjectDesignContent.class, cri).get(0);
		return dcProjectDesignContent;
	}
	
	/**
	 * 根据id查询项目设计内容
	 * @param id
	 * @return
	 */
	public DcProjectDesignContent getDesignContentById(String id){
		DcProjectDesignContent dcProjectDesignContent = dao().fetch(DcProjectDesignContent.class,id);
		return dcProjectDesignContent;
	}
	
	/**
	 * 查询专业下的设计内容
	 * @param major_id
	 * @return
	 */
	public List <DcProjectDesignContent> getDesignContentByMajorId(String major_id){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("project_design_major_id", "=", major_id).and("del_flag", "=", false);
		List <DcProjectDesignContent> content_list = dao().query(DcProjectDesignContent.class, criteria);
		return content_list;
	}
	
	/**
	 * 逻辑删除设计内容
	 * @param content_id
	 * @return
	 * @throws Exception 
	 */
	public int delProjectDesignContentById(String content_id) throws Exception{
		return delObj(content_id);
	}
	
	/**
	 * 更新设计内容
	 * @param dcProjectDesignContent
	 * @return
	 */
	public int updateProjectDesignContent(DcProjectDesignContent dcProjectDesignContent){
		return updateObj(dcProjectDesignContent);
	}
	
	
	/**
	 * 添加与设计内容相关的相关专业的关联关系
	 * @param major_list
	 * @param dcProjectDesignContent
	 * @return
	 */
	public int addProjectDesignMajor(List<DcProjectDesignMajor> major_list , DcProjectDesignContent dcProjectDesignContent){
		for(DcProjectDesignMajor dcProjectDesignMajor : major_list){
			DcProjectDesignContent parent = dcProjectDesignContent;
			List <DcProjectDesignMajor> son_list = new ArrayList<>();
			son_list.add(dcProjectDesignMajor);
			List <DcDesignContentMajor> list = getDesignContentMajorByMajorAndContent(dcProjectDesignMajor.getId(),dcProjectDesignContent.getId());
			if(list == null || list.isEmpty()){
				dao().insertRelation(parent,"majors");
			}
		}
		return 1;
	}
	
	public List<DcDesignContentMajor> getDesignContentMajorByMajorAndContent(String major_id , String content_id){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("project_design_content_id", "=", content_id).and("project_design_major_id", "=", major_id).and("del_flag", "=", false);
		return dao().query(DcDesignContentMajor.class, criteria);
	}
	
	
	/**
	 * 根据条件查询各设计内容已匹配的专业以及资料信息
	 * @return
	 */
	public List<Map> findContentMatchingMentions(NutMap nutMap){
		List <Map> page_list = null;
		String sqlStr = "select dpdc.id , dpdc.project_design_content_code , dpdc.project_design_content_name ,"
				+ " group_concat(distinct(dpdm.project_design_major_name)) project_design_major_name , group_concat(distinct(dpmi.mention_information_name)) mention_information_name , dpdp.project_design_phase_name"
				+ " from dc_project_design_content dpdc left join dc_design_content_major ddcm on dpdc.id = ddcm.project_design_content_id,"
				+ " dc_project_mention_information_content_major dpmicm , dc_project_mention_information dpmi , dc_project_design_major dpdm , dc_project_design_phase dpdp "
				+ " where ddcm.project_design_major_id = dpdm.id and ddcm.project_design_major_id = dpmicm.major_id"
				+ " and ddcm.project_design_content_id = dpmicm.content_id and dpmicm.project_mention_information_id = dpmi.id and"
				+ " dpmicm.project_design_phase_id = dpdp.id and "
				+ " dpdc.del_flag = '0' and ddcm.del_flag = '0' and dpmicm.del_flag = '0' and dpmi.del_flag = '0' and dpdm.del_flag = '0' and dpdp.del_flag = '0'";
		if (nutMap.get("content_name")!=null){
			sqlStr+= "and dpdc.project_design_content_name like @content_name ";
		}
		StringBuffer stringBuffer = new StringBuffer(sqlStr
				+ " group by dpdc.id  , dpdp.project_design_phase_name ");
		Sql sql = Sqls.create(stringBuffer.toString());
		if (nutMap.get("content_name")!=null){
			sql.params().set("content_name", "%"+nutMap.get("content_name")+"%");
		}
		sql.setCallback(new SqlCallback() {
			List<Map <String , Object>> list = new LinkedList<>();
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next()){
					Map <String , Object> map = new HashMap<>();
					map.put("id", rs.getString("id"));
					map.put("project_design_content_code", rs.getString("project_design_content_code"));
					map.put("project_design_content_name", rs.getString("project_design_content_name"));
					map.put("project_design_major_name", rs.getString("project_design_major_name"));
					map.put("mention_information_name", rs.getString("mention_information_name"));
					map.put("project_design_phase_name", rs.getString("project_design_phase_name"));
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(Map.class);
		return page_list;
	}

	//查询项目相关的设计内容
	public List <Map> findDesignContentByProjectId(NutMap nutMap){
		StringBuffer find_sql = new StringBuffer(" select * ");
		if(nutMap.get("project_id") != null){
			find_sql.append(" , (select pc_project_info.id from pc_project_info where find_in_set(dpdc.id,project_design_content_id) and id = @project_id) project_id ");
		}
		find_sql.append(" from dc_project_design_content dpdc where dpdc.del_flag = '0' ");
		if(nutMap.get("major_id") != null){
			find_sql.append(" and dpdc.project_design_major_id = @major_id");
		}
		Sql sql = Sqls.create(find_sql.toString());
		if(nutMap.get("project_id") != null){
			sql.params().set("project_id", nutMap.get("project_id"));
		}
		if(nutMap.get("major_id") != null){
			sql.params().set("major_id", nutMap.get("major_id"));
		}
		sql.setCallback(new SqlCallback() {
			List <Map> list = new ArrayList<>();
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next()){
					Map <String , Object> map = new HashMap<>();
					map.put("id", rs.getString("id"));
					map.put("project_design_content_code", rs.getString("project_design_content_code"));
					map.put("project_design_content_name", rs.getString("project_design_content_name"));
					if(nutMap.get("project_id") != null){
						map.put("project_id", rs.getString("project_id"));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		return sql.getList(Map.class);
	}
	
	/**
	 * 根据条件分页查询设计内容相关信息
	 * @param nutMap
	 * @return
	 */
	public QueryResult findDesignContentPageListAndInfoByCondition(NutMap nutMap){
		List <Map> page_list = null;
		Pager pager = null;
		String find_sql = " select ddc.*,ifnull(ddm.project_design_major_name,'') major_name "
				   + " from dc_project_design_content ddc "
				   + " left join dc_project_design_major ddm "
				   + " on ddc.project_design_major_id = ddm.id and ddm.del_flag = '0' "
				   + " where ddc.del_flag = '0' ";
		if(nutMap.get("content_code") != null){
			find_sql += " and ddc.project_design_content_code = @content_code";
		}
		if(nutMap.get("content_name") != null){
			find_sql += " and ddc.project_design_content_name like @content_name";
		}
		if(nutMap.get("major_name") != null){
			find_sql += " and ddm.project_design_major_name like @major_name";
		}
		if(nutMap.get("major_id") != null){
			find_sql += " and ddm.id = @major_id";
		}
		find_sql += " order by ddc.create_time ";
		Sql sql = Sqls.create(find_sql);
		if(nutMap.get("content_code") != null){
			sql.params().set("content_code", nutMap.get("content_code"));
		}
		if(nutMap.get("content_name") != null){
			sql.params().set("content_name", "%"+nutMap.get("content_name")+"%");
		}
		if(nutMap.get("major_name") != null){
			sql.params().set("major_name", "%"+nutMap.get("major_name")+"%");
		}
		if(nutMap.get("major_id") != null){
			sql.params().set("major_id", nutMap.get("major_id"));
		}
		if(nutMap.get("page_size") != null && nutMap.get("page_num") != null){
			pager = new Pager(nutMap.getInt("page_num"), nutMap.getInt("page_size"));
			pager.setRecordCount(this.findDesignContentPageListCount(nutMap));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			List<Map> list = new LinkedList<>();
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next()){
					Map<String,Object> map = new HashMap<>();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				SwapUtil.clearNull(list);
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(Map.class);
		return new QueryResult(page_list,pager);
	}

	/**
	 * 查询记录数
	 * @return
	 */
	public int findDesignContentPageListCount(NutMap nutMap){
		int count = 0;
		String find_sql = " select count(ddc.id) size_num "
				   + " from dc_project_design_content ddc "
				   + " left join dc_project_design_major ddm "
				   + " on ddc.project_design_major_id = ddm.id and ddm.del_flag = '0' "
				   + " where ddc.del_flag = '0' ";
		if(nutMap.get("content_code") != null){
			find_sql += " and ddc.project_design_content_code = @content_code";
		}
		if(nutMap.get("content_name") != null){
			find_sql += " and ddc.project_design_content_name like @content_name";
		}
		if(nutMap.get("major_name") != null){
			find_sql += " and ddm.project_design_major_name like @major_name ";
		}
		Sql sql = Sqls.create(find_sql);
		if(nutMap.get("content_code") != null){
			sql.params().set("content_code", nutMap.get("content_code"));
		}
		if(nutMap.get("content_name") != null){
			sql.params().set("content_name", "%"+nutMap.get("content_name")+"%");
		}
		if(nutMap.get("major_name") != null){
			sql.params().set("major_name", "%"+nutMap.get("major_name")+"%");
		}
		sql.setCallback(new SqlCallback() {
			int count = 0;
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next())
				count = rs.getInt("size_num");
				return count;
			}
		});
		dao().execute(sql);
		return sql.getInt();
	}
	
	
	
	
	

}
